﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data;

namespace UsersReg
{
    public struct Users
    {
        public string user, pass, name, position;
    }
    public struct ProductDetails
    {
        public string proName, qty, price;
    }
    public struct CostumerDetails
    {
        public string CosName, Contact, Sex, Address;
    }
    public struct SupplierDetails
    {
        public string SuppName, Contact, Sex, Address;
    }

    public struct updateQty
    {
        public string qty;
    }

    public struct ProductSearch
    {
        public string proName, qty, price;
    }


    public class dBase
    {
        OdbcConnection oc = new OdbcConnection();
        OdbcDataAdapter od = new OdbcDataAdapter();
        public DataTable dtl = new DataTable();


        public dBase() { }
        public void connect()
        {
            oc.ConnectionString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)}"
                + ";DBQ=.//Registration.accdb;PWD=";
            oc.Open();
        }
        public void query(string sql)
        {
            dtl = new DataTable();
            od.SelectCommand = new OdbcCommand(sql, oc);
            dtl.Clear();
            dtl.Reset();
            od.Fill(dtl);
        }



        void update()
        {
            OdbcCommandBuilder ocb = new OdbcCommandBuilder(od);
            od.Update(dtl);

        }

        public void registerUsers(Users list)
        {
            DataRow row = dtl.NewRow();
            row[1] = list.user;
            row[2] = list.pass;
            row[3] = list.name;
            row[4] = list.position;
            dtl.Rows.Add(row);
            update();
        }


        public void productView()
        {
            dtl = new DataTable();
            od.SelectCommand = new OdbcCommand("SELECT * FROM tblProducts", oc);
            dtl.Clear();
            dtl.Reset();
            od.Fill(dtl);
        }

        public void CostumerView()
        {
            dtl = new DataTable();
            od.SelectCommand = new OdbcCommand("SELECT * FROM tblCostumers", oc);
            dtl.Clear();
            dtl.Reset();
            od.Fill(dtl);
        }
        public void SupplierView()
        {
            dtl = new DataTable();
            od.SelectCommand = new OdbcCommand("SELECT * FROM tblSuppliers", oc);
            dtl.Clear();
            dtl.Reset();
            od.Fill(dtl);
        }


        public void UsersView()
        {
            dtl = new DataTable();
            od.SelectCommand = new OdbcCommand("SELECT * FROM tblUsers", oc);
            dtl.Clear();
            dtl.Reset();
            od.Fill(dtl);
        }
        public void addProductRecord(ProductDetails product)
        {
            DataRow dr = dtl.NewRow();
            dr[1] = product.proName;
            dr[2] = product.qty;
            dr[3] = product.price;
            dtl.Rows.Add(dr);
            update();
        }

        public void AddCostumerRecord(CostumerDetails costumer)
        {
            DataRow dr = dtl.NewRow();
            dr[1] = costumer.CosName;
            dr[2] = costumer.Contact;
            dr[3] = costumer.Sex;
            dr[4] = costumer.Address;
            dtl.Rows.Add(dr);
            update();
        }

        public void AddSupplierRecord(SupplierDetails supplier)
        {
            DataRow dr = dtl.NewRow();
            dr[1] = supplier.SuppName;
            dr[2] = supplier.Contact;
            dr[3] = supplier.Sex;
            dr[4] = supplier.Address;
            dtl.Rows.Add(dr);
            update();
        }


        public void editProductRecord(ProductDetails pro, int i)
        {
            dtl.Rows[i][1] = pro.proName;
            dtl.Rows[i][2] = pro.qty;
            dtl.Rows[i][3] = pro.price;
            update();
        }
        public void editCostumerRecord(CostumerDetails cost, int i)
        {
            dtl.Rows[i][1] = cost.CosName;
            dtl.Rows[i][2] = cost.Contact;
            dtl.Rows[i][3] = cost.Sex;
            dtl.Rows[i][4] = cost.Address;
            update();
        }
        public void editSupplierRecord(SupplierDetails supp, int i)
        {
            dtl.Rows[i][1] = supp.SuppName;
            dtl.Rows[i][2] = supp.Contact;
            dtl.Rows[i][3] = supp.Sex;
            dtl.Rows[i][4] = supp.Address;
            update();
        }


        public void editUsers(Users accnt, int i)
        {
            dtl.Rows[i][1] = accnt.user;
            dtl.Rows[i][2] = accnt.pass;
            dtl.Rows[i][3] = accnt.name;
            dtl.Rows[i][4] = accnt.position;
            update();
        }
        public void QtyUpdate(updateQty quantity, int i)
        {
            dtl.Rows[i][2] = quantity.qty;
            update();
        }

        public void close()
        {
            oc.Close();
        }
    }
}